﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using QuanLyNhaSach.QuanLyNhaSachDTO;

namespace QuanLyNhaSach.QuanLyNhaSachDao
{
    class KhachHangDao
    {
        public static DataTable GetKhachHangAll()
        {
            string sql = "select * from KhachHang where Active = True";
            return Database.ExcuQuery(sql);
        }
        public static bool KTTen(string ten)
        {
            string sql = "select * from KhachHang";
            DataTable dt = Database.ExcuQuery(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
                if (dt.Rows[i]["TenKhachHang"].ToString() == ten && dt.Rows[i]["Active"].ToString() == "false")
                    return true;
            return false;
        }
        public static bool KTTenTT(string ten)
        {
            string sql = "select * from KhachHang";
            DataTable dt = Database.ExcuQuery(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
                if (dt.Rows[i]["TenKhachhang"].ToString() == ten && dt.Rows[i]["Active"].ToString() == "True")
                    return true;
            return false;
        }
        public static bool Insert(KhachHangDTO kh)
        {
            string sql;
            bool active = true;
            if (KTTen(kh.TenKhachHang) == true)
            {
                sql = "Update KhachHang set Active= " + active + " where TenKhachHang= " + kh.TenKhachHang + "";
                Database.ExcuNonQuery(sql);
                return true;
            }
            if (KTTen(kh.TenKhachHang) == false && KTTenTT(kh.TenKhachHang) == false)
            {
                sql = "insert into KhachHang(TenKhachHang,DiaChi,Email,DienThoai,TienNo,Active) values('" + kh.TenKhachHang + "','" + kh.DiaChi + "','" + kh.Email + "','" + kh.DienThoai + "'," + kh.TienNo + "," + active + ")";
                Database.ExcuNonQuery(sql);
                return true;
            }
            return false;
        }
        public static void Delete(KhachHangDTO kh)
        {
            bool active = false;
            string sql = "Update KhachHang set Active= " + active + " where TenKhachHang= '" + kh.TenKhachHang + "'";
            Database.ExcuNonQuery(sql);
        }
        public static void Update(KhachHangDTO kh)
        {
            string sql = "Update KhachHang set TenKhachHang= '" + kh.TenKhachHang + "',DiaChi= '" + kh.DiaChi + "',Email= '" + kh.Email + "',DienThoai= '" + kh.DienThoai + "',TienNo= " + kh.TienNo + " where MaKhachHang= " + kh.MaKhachHang + "";
            Database.ExcuNonQuery(sql);
        }
        ////////////////////Tra cứu//////////////////////////////
        public static DataTable KhachHangTraCuuTen(string ten)
        {
            string sql = "select * from KhachHang where TenKhachHang LIKE '%" + ten + "%' and Active=True ";
            return Database.ExcuQuery(sql);
        }
        public static DataTable KhachHangTraCuuDiaChi(string diachi)
        {
            string sql = "select * from KhachHang where DiaChi LIKE '%" + diachi + "%' and Active=True ";
            return Database.ExcuQuery(sql);
        }
        public static DataTable KhachHangTraCuuDienThoai(string dienthoai)
        {
            string sql = "select * from KhachHang where DienThoai LIKE '%" + dienthoai + "%' and Active=True ";
            return Database.ExcuQuery(sql);
        }
        public static DataTable KhachHangTraCuuEmail(string email)
        {
            string sql = "select * from KhachHang where Email LIKE '%" + email + "%' and Active=True ";
            return Database.ExcuQuery(sql);
        }
        public static DataTable KhachHangTraCuuTienNo(string tienno)
        {
            string sql = "select * from KhachHang where TienNo = " + tienno + " and Active=True ";
            return Database.ExcuQuery(sql);
        }
    }
}
